/* eslint-disable @typescript-eslint/no-explicit-any */
// 此文件处理分页查询，需要处理动态表结构

import type { SQL } from 'drizzle-orm';
import { desc, asc, count, sql } from 'drizzle-orm';
import type { PgTable } from 'drizzle-orm/pg-core';
import type { Database } from '../client';
import type { IPaginationQuery, IPaginationResponse } from '@nbase/shared';
import { PAGINATION } from '@nbase/shared';

/**
 * 分页配置常量（从 shared 导入）
 */
const { DEFAULT_PAGE, DEFAULT_PAGE_SIZE, MAX_PAGE_SIZE } = PAGINATION;

/**
 * 执行分页查询
 * 
 * @param db 数据库实例
 * @param table 表定义
 * @param query 分页参数（来自前端）
 * @param where 查询条件（可选，后端专属）
 * @returns 分页结果
 * 
 * @example
 * // 基础分页
 * const result = await paginate(db, sysUser, { page: 1, pageSize: 10 });
 * 
 * // 带条件和排序
 * const result = await paginate(
 *   db,
 *   sysUser,
 *   { page: 1, pageSize: 10, sortBy: 'createdAt', sortOrder: 'desc' },
 *   eq(sysUser.status, 1)
 * );
 */
export async function paginate<TTable extends PgTable>(
  db: Database,
  table: TTable,
  query: IPaginationQuery = {},
  where?: SQL
): Promise<IPaginationResponse<any>> {
  const {
    page: rawPage = DEFAULT_PAGE,
    pageSize: rawPageSize = DEFAULT_PAGE_SIZE,
    sortBy,
    sortOrder = 'desc',
  } = query;

  // 边界检查和限制
  const page = Math.max(rawPage, 1);  // 最小为 1
  const pageSize = Math.min(Math.max(rawPageSize, 1), MAX_PAGE_SIZE);  // 1-100
  const offset = (page - 1) * pageSize;

  // 构建查询
  let queryBuilder = db.select().from(table as any);

  // 添加 where 条件
  if (where) {
    queryBuilder = queryBuilder.where(where) as any;
  }

  // 添加排序
  if (sortBy) {
    const tableAny = table as any;
    const sortField = tableAny[sortBy];
    if (sortField) {
      queryBuilder = queryBuilder.orderBy(
        sortOrder === 'asc' ? asc(sortField) : desc(sortField)
      ) as any;
    }
  }

  // 执行查询
  const [items, totalResult] = await Promise.all([
    // 查询当前页数据
    queryBuilder.limit(pageSize).offset(offset),
    // 查询总数
    db.select({ count: count() }).from(table as any).where(where || undefined),
  ]);

  const total = Number(totalResult[0]?.count || 0);  // 强制转换（PostgreSQL bigint 返回 string）
  const totalPages = Math.ceil(total / pageSize);

  return {
    items,
    total,
    page,
    pageSize,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

/**
 * 构建分页元数据
 * 
 * 用于复杂查询场景，业务层自己执行查询后，使用此函数构建分页元数据
 * 
 * @param total 总记录数
 * @param page 当前页码
 * @param pageSize 每页数量
 * @returns 分页元数据（不含 items）
 * 
 * @example
 * const items = await db.select()...complex query...;
 * const [{ count: total }] = await db.select({ count: count() })...;
 * 
 * return {
 *   items,
 *   ...buildPaginationMeta(total, query.page || 1, query.pageSize || 10)
 * };
 */
export function buildPaginationMeta(
  total: number,
  page: number = DEFAULT_PAGE,
  pageSize: number = DEFAULT_PAGE_SIZE
): Omit<IPaginationResponse<any>, 'items'> {
  const totalPages = Math.ceil(total / pageSize);

  return {
    total,
    page,
    pageSize,
    totalPages,
    hasNext: page < totalPages,
    hasPrev: page > 1,
  };
}

/**
 * 对已构建的 QueryBuilder 进行分页
 * 
 * 用于联表查询等复杂场景
 * 
 * @param queryBuilder Drizzle QueryBuilder（已包含 select, from, join, where, orderBy）
 * @param query 分页参数
 * @param totalCount 总记录数
 * @returns 分页结果
 * 
 * @example
 * // 构建联表查询
 * const queryBuilder = db
 *   .select({
 *     id: sysUser.id,
 *     username: sysUser.username,
 *     roleName: sysRole.name
 *   })
 *   .from(sysUser)
 *   .leftJoin(sysUserRole, eq(sysUser.id, sysUserRole.userId))
 *   .leftJoin(sysRole, eq(sysUserRole.roleId, sysRole.id))
 *   .where(eq(sysUser.isDeleted, false))
 *   .orderBy(desc(sysUser.createdAt));
 * 
 * // 查询总数
 * const [{ count: total }] = await db
 *   .select({ count: count() })
 *   .from(sysUser)
 *   .where(eq(sysUser.isDeleted, false));
 * 
 * // 执行分页
 * return paginateQueryBuilder<UserWithRole>(queryBuilder, query, total);
 */
export async function paginateQueryBuilder<T = any>(
  queryBuilder: any,
  query: IPaginationQuery = {},
  totalCount: number
): Promise<IPaginationResponse<T>> {
  const { page: rawPage = DEFAULT_PAGE, pageSize: rawPageSize = DEFAULT_PAGE_SIZE } = query;
  const page = Math.max(rawPage, 1);
  const pageSize = Math.min(Math.max(rawPageSize, 1), MAX_PAGE_SIZE);
  const offset = (page - 1) * pageSize;

  // 执行分页查询
  const items = await queryBuilder.limit(pageSize).offset(offset);

  return {
    items,
    ...buildPaginationMeta(totalCount, page, pageSize),
  };
}

/**
 * 使用原生 SQL 模板进行分页查询
 * 
 * 适用于复杂的 SQL 查询（窗口函数、递归 CTE、JSON 聚合等）
 * 
 * @param db 数据库实例
 * @param options SQL 配置
 * @returns 分页结果
 * 
 * @example
 * import { sql } from 'drizzle-orm';
 * 
 * // 复杂的聚合查询
 * const result = await paginateWithSql<UserWithStats>(db, {
 *   select: sql`
 *     SELECT 
 *       u.id,
 *       u.username,
 *       COUNT(DISTINCT p.id) as post_count,
 *       json_agg(json_build_object('id', r.id, 'name', r.name)) as roles
 *   `,
 *   from: sql`
 *     FROM sys_user u
 *     LEFT JOIN posts p ON u.id = p.author_id
 *     LEFT JOIN sys_user_role ur ON u.id = ur.user_id
 *     LEFT JOIN sys_role r ON ur.role_id = r.id
 *   `,
 *   where: sql`u.is_deleted = false`,
 *   groupBy: sql`u.id, u.username`,           // ✅ 不含 GROUP BY
 *   orderBy: sql`post_count DESC`,            // ✅ 不含 ORDER BY
 *   query: { page: 1, pageSize: 10 }
 * });
 */
export async function paginateWithSql<T = any>(
  db: Database,
  options: {
    select: SQL;
    from: SQL;
    where?: SQL;         // WHERE 条件（不含 WHERE 关键字）
    groupBy?: SQL;       // GROUP BY 字段（不含 GROUP BY 关键字）
    having?: SQL;        // HAVING 条件（不含 HAVING 关键字）
    orderBy?: SQL;       // ORDER BY 子句（不含 ORDER BY 关键字）
    query: IPaginationQuery;
  }
): Promise<IPaginationResponse<T>> {
  const { select, from, where, groupBy, having, orderBy, query: paginationQuery } = options;
  const { page: rawPage = DEFAULT_PAGE, pageSize: rawPageSize = DEFAULT_PAGE_SIZE } = paginationQuery;
  const page = Math.max(rawPage, 1);
  const pageSize = Math.min(Math.max(rawPageSize, 1), MAX_PAGE_SIZE);
  const offset = (page - 1) * pageSize;

  // 构建数据查询 SQL（统一由函数添加关键字）
  const parts: SQL[] = [select, from];
  if (where) parts.push(sql`WHERE ${where}`);
  if (groupBy) parts.push(sql`GROUP BY ${groupBy}`);
  if (having) parts.push(sql`HAVING ${having}`);
  if (orderBy) parts.push(sql`ORDER BY ${orderBy}`);
  
  const dataQuery = sql.join(parts, sql.raw(' '));
  const dataQueryWithPagination = sql`${dataQuery} LIMIT ${pageSize} OFFSET ${offset}`;

  // 构建计数查询 SQL
  // 如果有 GROUP BY，需要用子查询
  let countQuery: SQL;
  if (groupBy) {
    const countParts: SQL[] = [select, from];
    if (where) countParts.push(sql`WHERE ${where}`);
    if (groupBy) countParts.push(sql`GROUP BY ${groupBy}`);
    if (having) countParts.push(sql`HAVING ${having}`);
    
    const subQuery = sql.join(countParts, sql.raw(' '));
    countQuery = sql`SELECT COUNT(*) as count FROM (${subQuery}) as subquery`;
  } else {
    const countParts: SQL[] = [sql`SELECT COUNT(*) as count`, from];
    if (where) countParts.push(sql`WHERE ${where}`);
    countQuery = sql.join(countParts, sql.raw(' '));
  }

  // 并行执行
  const [itemsResult, totalResult] = await Promise.all([
    db.execute(dataQueryWithPagination),
    db.execute(countQuery),
  ]);

  // postgres-js 驱动：db.execute() 直接返回数组
  const items = itemsResult as T[];
  const totalRow = (totalResult as any[])[0];
  const total = Number(totalRow?.count || 0);

  return {
    items,
    ...buildPaginationMeta(total, page, pageSize),
  };
}

/**
 * 使用完整 SQL 进行分页查询
 * 
 * 适用于极其复杂的 SQL（CTE、子查询、窗口函数等）
 * 需要手动提供数据查询和计数查询
 * 
 * @param db 数据库实例
 * @param dataSql 数据查询 SQL（不含 LIMIT OFFSET，函数会自动添加）
 * @param countSql 计数查询 SQL（返回 count 字段）
 * @param query 分页参数
 * @returns 分页结果
 * 
 * @example
 * import { sql } from 'drizzle-orm';
 * 
 * // CTE 递归查询（完整 SQL，已含 ORDER BY）
 * const dataSql = sql`
 *   WITH RECURSIVE menu_tree AS (
 *     SELECT *, 0 as level FROM sys_menu WHERE parent_id IS NULL
 *     UNION ALL
 *     SELECT m.*, t.level + 1 
 *     FROM sys_menu m 
 *     JOIN menu_tree t ON m.parent_id = t.id
 *   )
 *   SELECT * FROM menu_tree
 *   ORDER BY level, sort
 * `;
 * 
 * const countSql = sql`
 *   WITH RECURSIVE menu_tree AS (
 *     SELECT * FROM sys_menu WHERE parent_id IS NULL
 *     UNION ALL
 *     SELECT m.* FROM sys_menu m JOIN menu_tree t ON m.parent_id = t.id
 *   )
 *   SELECT COUNT(*) as count FROM menu_tree
 * `;
 * 
 * const result = await paginateFullSql<MenuItem>(db, dataSql, countSql, query);
 */
export async function paginateFullSql<T = any>(
  db: Database,
  dataSql: SQL,
  countSql: SQL,
  query: IPaginationQuery = {}
): Promise<IPaginationResponse<T>> {
  const { page: rawPage = DEFAULT_PAGE, pageSize: rawPageSize = DEFAULT_PAGE_SIZE } = query;
  const page = Math.max(rawPage, 1);
  const pageSize = Math.min(Math.max(rawPageSize, 1), MAX_PAGE_SIZE);
  const offset = (page - 1) * pageSize;

  // 在数据查询后添加 LIMIT OFFSET
  const dataQueryWithPagination = sql`${dataSql} LIMIT ${pageSize} OFFSET ${offset}`;

  // 并行执行
  const [itemsResult, totalResult] = await Promise.all([
    db.execute(dataQueryWithPagination),
    db.execute(countSql),
  ]);

  // postgres-js 驱动：db.execute() 直接返回数组
  const items = itemsResult as T[];
  const totalRow = (totalResult as any[])[0];
  const total = Number(totalRow?.count || 0);

  return {
    items,
    ...buildPaginationMeta(total, page, pageSize),
  };
}


